package fr.mywiki.model.queries;

import java.io.ByteArrayInputStream;
import java.rmi.RemoteException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import fr.mywiki.business.file.FileLite;
import fr.mywiki.business.file.FileManager;
import fr.mywiki.model.ejbAdapter.utils.PersistenceException;
import fr.mywiki.model.ejbAdapter.utils.Sequencer;

/**
 * Retrieves, stores and manipulates the files stored in the database.
 * 
 * @author tlombard
 */
public class DatabaseFileController {
	/**
	 * Creates a new file object in the database, this object having the
	 * specified identifier.
	 * 
	 * @param id the identifier of the object to create.
	 * @return a <code>FileLite</code> object representing the created file.
	 * @throws SQLException if the insertion could not be done.
	 */
	protected static FileLite createFile(Long id) throws SQLException {
		String sql = "INSERT INTO file_ VALUES (" + id + ", NULL, NULL)";
		DatabaseAdapter.executeUpdateQuery(sql);
		
		FileLite file = new FileLite();
		file.setId(id);
		return file;
	}
	
	/**
	 * Creates a new file_ entry in the database and returns a FileLite linked to it.
	 * 
	 * @return an empty <code>FileLite</code> object.
	 * @throws RemoteException if a SQLException was caught.
	 */
	public static FileLite createFile() throws PersistenceException, RemoteException {
		Long key = new Long(Sequencer.getInstance(FileLite.JNDI_NAME).next());
		try {
			while (DatabaseFileController.findFileByPrimaryKey(key) != null)
				key = new Long(Sequencer.getInstance(FileLite.JNDI_NAME).next());
			return createFile(key);
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		} 
	}
	
	/**
	 * Updates a file object in the database. The file object must already exist.
	 * 
	 * @param file a <code>FileLite</code> object.
	 * @throws RemoteException if a SQLException has been caught.
	 * TODO test light/not light.
	 */
	public static void updateFile(FileLite file) throws RemoteException {
		try {
			Connection con = DatabaseAdapter.makeConnection();
			String sql = "UPDATE file_ SET name=?, content=? WHERE id="
				+ file.getId().toString();
			
			PreparedStatement stat = con.prepareStatement(sql);
			stat.setString(1, file.getName());
			if (!file.isLightValue())
				stat.setBinaryStream(2,
						new ByteArrayInputStream(file.getContent()),
						file.getContent().length);
			stat.execute();
			DatabaseAdapter.closeConnection(con);
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}
	}
	
	/**
	 * Retrieves the file object whose identifier is given in parameter.
	 * 
	 * @param id a <code>Long</code> object being the identifier of the file
	 *          object.
	 * @param type specifies if the object returned should be
	 *          <code>TYPE_ENT_LIGHT</code> or not.
	 * @return a <code>FileLite</code> object if it has been found in the
	 *         database. Eventually a light one.
	 * @throws RemoteException if a SQLException was caught.
	 */
	public static FileLite findFileByPrimaryKey(Long id, String type) throws RemoteException {
		try {
			Connection con = DatabaseAdapter.makeConnection();
			String sql = "SELECT id, name, content FROM file_ WHERE id=" + id;

			PreparedStatement stat = con.prepareStatement(sql);
			ResultSet resultSet = stat.executeQuery();
			
			if (resultSet.next()) {
				if (FileLite.TYPE_ENT_LIGHT.equals(type))
					return new FileLite(
							new Long(resultSet.getLong(1)),
							resultSet.getString(2));
				Blob blob = resultSet.getBlob(3);
				return new FileLite(
						new Long(resultSet.getLong(1)),
						resultSet.getString(2), 
						blob.getBytes(1L, (int) blob.length()));
			} else return null;
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}
	}
	
	/**
	 * Retrieves the file object whose name is given in parameter. The parameter
	 * <code>name<code> and the content of the column <tt>name</tt> of the file object must be exactly the same.  
	 * 
	 * @param id the name of the file object.
	 * @param type specifies if the object returned should be
	 *          <code>TYPE_ENT_LIGHT</code> or not.
	 * @return a <code>FileLite</code> object if it has been found in the
	 *         database. Eventually a light one.
	 * @throws RemoteException if a SQLException was caught.
	 * TODO test it.
	 */
	public static FileLite findFileByName(String name, String type) throws RemoteException {
		try {
			Connection con = DatabaseAdapter.makeConnection();
			String sql = "SELECT id, name, content FROM file_ WHERE name LIKE '" + name+"'";

			PreparedStatement stat = con.prepareStatement(sql);
			ResultSet resultSet = stat.executeQuery();
			
			if (resultSet.next()) {
				if (FileLite.TYPE_ENT_LIGHT.equals(type))
					return new FileLite(
							new Long(resultSet.getLong(1)),
							resultSet.getString(2));
				Blob blob = resultSet.getBlob(3);
				return new FileLite(
						new Long(resultSet.getLong(1)),
						resultSet.getString(2), 
						blob.getBytes(1L, (int) blob.length()));
			} else return null;
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}
	}
	
	/**
	 * Retrieves the file object whose name is given in parameter. The parameter
	 * <code>name<code> and the content of the column <tt>name</tt> of the file object must be exactly the same.  
	 * 
	 * @see #findFileByName(String, String)
	 */
	public static FileLite findFileByName(String name) throws RemoteException {
		return findFileByName(name, FileLite.TYPE_ENT);
	}
	
	/**
	 * Retrieves the file object (non light, includes the content) whose
	 * identifier is given in parameter.
	 * 
	 * @see DatabaseFileController#findFileByPrimaryKey(Long, String)
	 */
	public static FileLite findFileByPrimaryKey(Long id) throws RemoteException {
		return findFileByPrimaryKey(id, FileLite.TYPE_ENT);
	}
	
	/**
	 * Returns all the file objects of the database.
	 * 
	 * @return a <code>Collection</code> of <code>File</code> objects
	 */
	public static Collection findAllFile() throws RemoteException {
		Collection objects = new ArrayList();
		try {
			Connection con = DatabaseAdapter.makeConnection();
			String sql = "SELECT id, name, content FROM file_";

			PreparedStatement stat = con.prepareStatement(sql);
			ResultSet resultSet = stat.executeQuery();

			while (resultSet.next()) {
				Blob blob = resultSet.getBlob(3);
				objects.add(
					new FileLite(new Long(resultSet.getLong(1)),
					resultSet.getString(2),
					blob == null ? 
						null : blob.getBytes(1L, (int) blob.length())));
			}
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}
		
		return objects;
	}
	
	/**
	 * Returns the file descriptors (without the data) of all the file objects
	 * of the database.
	 * TODO test it !
	 * 
	 * @return a <code>Collection</code> of <code>File</code> objects
	 *         without data
	 */
	public static Collection findAllFileLight() throws RemoteException {
		Collection objects = new ArrayList();
		try {
			Connection con = DatabaseAdapter.makeConnection();
			String sql = "SELECT id, name FROM file_";

			PreparedStatement stat = con.prepareStatement(sql);
			ResultSet resultSet = stat.executeQuery();

			while (resultSet.next())
				objects.add(new FileLite(new Long(resultSet.getLong(1)),
						resultSet.getString(2)));
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}

		return objects;
	}
	
	/**
	 * Returns the file descriptors (without the data) of all the image file
	 * objects of the database. Image files are recognized by the extension of the
	 * name of file which is in <code>FileManager.IMAGE_EXTENSIONS</code>.
	 * 
	 * @return a <code>Collection</code> of <code>File</code> objects without
	 *         data
	 * TODO test it !
	 */
	public static Collection findAllImageFileLight() throws RemoteException {
		Collection objects = new ArrayList();
		
		StringBuffer where = new StringBuffer();
		boolean bFirst = true;
		for(int i=0;i<FileManager.IMAGE_EXTENSIONS.length;i++) {
			if(!bFirst)
				where.append(" OR ");
			where.append(" name LIKE '%."+FileManager.IMAGE_EXTENSIONS[i]+"' ");
			bFirst = false;
		}
		
		try {
			Connection con = DatabaseAdapter.makeConnection();
			String sql = "SELECT id, name FROM file_ WHERE " + where;

			PreparedStatement stat = con.prepareStatement(sql);
			ResultSet resultSet = stat.executeQuery();

			while (resultSet.next())
				objects.add(new FileLite(new Long(resultSet.getLong(1)),
						resultSet.getString(2)));
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}

		return objects;
	}
	
	/**
	 * Removes a record from the database.
	 * 
	 * @param id <code>Long</code> identifier of the record to remove.
	 * @throws RemoteException if a SQLException was caught.
	 */
	public static void removeFile(Long id) throws RemoteException {
		String sql = "DELETE FROM file_ WHERE id="+id.toString();
		try {
			DatabaseAdapter.executeUpdateQuery(sql);
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}
	}
	
	/**
	 * Returns all the file descriptors corresponding whose name corresponds to
	 * the pattern and whose extension is the one given in parameter.
	 * 
	 * @param pattern a pattern to recognize the name of the file
	 * @param extension the extension of the name of the file
	 * @return a <code>Collection</code> of light <code>FileLite</code>
	 *         objects.
	 * TODO Test it
	 */
	public static Collection findAllFileLight(String pattern, String extension) throws RemoteException {
		Collection objects = new ArrayList();
		try {
			Connection con = DatabaseAdapter.makeConnection();
			String sql = "SELECT id, name FROM file_ WHERE name LIKE ";

			PreparedStatement stat = con.prepareStatement(sql);
			ResultSet resultSet = stat.executeQuery();

			while (resultSet.next())
				objects.add(new FileLite(new Long(resultSet.getLong(1)),
						resultSet.getString(2)));
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}

		return objects;
	}
	
	/**
	 * Returns the content of the file represented by the <code>FileLite</code>
	 * object.
	 * 
	 * @param file a <code>FileLite</code> object, light or not.
	 * @return a array of bytes representing the content of the file.
	 * @throws RemoteException if a RemoteException was thrown.
	 */
	public static byte[] getFileContent(FileLite file) throws RemoteException  {
		if (!file.isLightValue())
			return file.getContent();
		try {
			Connection con = DatabaseAdapter.makeConnection();
			String sql = "SELECT content FROM file_ WHERE id=" + file.getId();

			PreparedStatement stat = con.prepareStatement(sql);
			ResultSet resultSet = stat.executeQuery();
			
			if (resultSet.next()) {
				Blob blob = resultSet.getBlob(1);
				return blob.getBytes(1L, (int) blob.length());
			}
		} catch (SQLException e) {
			throw new RemoteException(e.getMessage());
		}
		
		return null;
	}
}
